package com.dao.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.dao.ProblemItemDAO;
import com.dto.ProblemItemDTO;
import com.util.db.DBUtil;

public class ProblemItemDAOImpl implements ProblemItemDAO {
	private Connection conn;

	public ProblemItemDAOImpl() {
		conn = DBUtil.getConnection();
	}

	@Override
	public ProblemItemDTO getProblemItem(String c_id) {
		ProblemItemDTO problemItemDTO = null;
		String sql = "SELECT * FROM t_base_problem_item WHERE c_id=?;";
		QueryRunner qr = new QueryRunner();

		try {
			problemItemDTO = qr
					.query(conn, sql, new BeanHandler<ProblemItemDTO>(
							ProblemItemDTO.class), c_id);
		} catch (SQLException e) {
			return null;
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return problemItemDTO;
	}

	@Override
	public List<ProblemItemDTO> getProblemItems(String c_id) {
		List<ProblemItemDTO> problemItems = null;
		String sql = "SELECT * FROM t_base_problem_item WHERE c_id=?;";
		QueryRunner qr = new QueryRunner();

		try {
			problemItems = qr.query(conn, sql,
					new BeanListHandler<ProblemItemDTO>(ProblemItemDTO.class),
					c_id);
		} catch (SQLException e) {
			return null;
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return problemItems;
	}

	@Override
	public List<ProblemItemDTO> getProblemItems() {
		List<ProblemItemDTO> problemItems = null;
		String sql = "SELECT * FROM t_base_problem_item;";
		QueryRunner qr = new QueryRunner();

		try {
			problemItems = qr.query(conn, sql,
					new BeanListHandler<ProblemItemDTO>(ProblemItemDTO.class));
		} catch (SQLException e) {
			return null;
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return problemItems;
	}

	@Override
	public boolean save(ProblemItemDTO problemItemDTO) {
		boolean result = true;
		int insertRows = 0;
		String sql = "INSERT INTO t_base_problem_item VALUES(?, ?, ?, ?, ?, ?, ?, ?);";
		QueryRunner qr = new QueryRunner();

		Object[] params = new Object[8];
		params[0] = problemItemDTO.getP_id();
		params[1] = problemItemDTO.getP_content();
		params[2] = problemItemDTO.getPt_id();
		params[3] = problemItemDTO.getChooise_content();
		params[4] = problemItemDTO.getAnswer();
		params[5] = problemItemDTO.getP_common_info();
		params[6] = problemItemDTO.getC_id();
		params[7] = problemItemDTO.getRemark();

		try {
			insertRows = qr.update(conn, sql, params);
		} catch (SQLException e) {
			return false;
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		result = (insertRows == 0) ? false : true;
		return result;
	}

	@Override
	public boolean delete(String p_id) {
		boolean result = true;
		int deleteRows = 0;
		String sql = "DELETE FROM t_base_problem_item WHERE p_id=?;";
		QueryRunner qr = new QueryRunner();

		try {
			deleteRows = qr.update(conn, sql, p_id);
		} catch (SQLException e) {
			return false;
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		result = (deleteRows == 0) ? false : true;
		return result;
	}

	@Override
	public boolean update(ProblemItemDTO problemItemDTO) {
		boolean result = true;
		int updateRows = 0;
		String sql = "UPDATE t_base_problem_item SET p_content=?, pt_id=?, chooise_content=?,"
				+ "answer=?, p_common_info=?, c_id=?, remark=? WHERE p_id=?;";
		QueryRunner qr = new QueryRunner();

		Object[] params = new Object[8];
		params[0] = problemItemDTO.getP_content();
		params[1] = problemItemDTO.getPt_id();
		params[2] = problemItemDTO.getChooise_content();
		params[3] = problemItemDTO.getAnswer();
		params[4] = problemItemDTO.getP_common_info();
		params[5] = problemItemDTO.getC_id();
		params[6] = problemItemDTO.getRemark();
		params[7] = problemItemDTO.getP_id();

		try {
			updateRows = qr.update(conn, sql, params);
		} catch (SQLException e) {
			return false;
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		result = (updateRows == 0) ? false : true;
		return result;
	}

	@Override
	public String getProblemMaxId() {
		String maxId = null;
		ProblemItemDTO problemItem = null;
		String sql = "SELECT MAX(p_id) AS p_id FROM t_base_problem_item;";
		QueryRunner qr = new QueryRunner();

		try {
			problemItem = qr.query(conn, sql, new BeanHandler<ProblemItemDTO>(
					ProblemItemDTO.class));
		} catch (SQLException e) {
			return "";
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		maxId = (problemItem != null) ? problemItem.getP_id() : null;
		return maxId;
	}

	@Override
	public List<ProblemItemDTO> getProblemItems(String course_id, String pt_id) {
		List<ProblemItemDTO> problemItems = null;
		StringBuilder sql = new StringBuilder(
				"SELECT * FROM t_base_problem_item WHERE c_id='" + course_id
						+ "'");

		if (pt_id != null && !"".equals(pt_id)) {
			sql.append(" AND pt_id='" + pt_id + "'");
		}
		sql.append(";");

		QueryRunner qr = new QueryRunner();

		try {
			problemItems = qr.query(conn, sql.toString(),
					new BeanListHandler<ProblemItemDTO>(ProblemItemDTO.class));
		} catch (SQLException e) {
			return null;
		} finally {
			try {
				DbUtils.close(conn);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return problemItems;
	}
}
